﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Windows.Forms;
using Control;

namespace LPV_POS
{
    public class thuvien
    {
        public thuvien()
        {
            if (Chuoi_CSDL.IndexOf("@") >= 0)
                this.Connect();
        }
        // khai bao cac bien thanh vien
        public static String Chuoi_CSDL = @"Server=@Server; Database=@Database; uid=@uid; Password=@Password;";
        private SqlConnection mKet_noi;
        // Lien ket voi mot stored proceduce 

        //Lay du lieu tu 1 store procedure, ket qua tra ve la mot table
        public DataTable SP_SelectDataTable(String sp_name, string[] paramse, params Object[] values)
        {
           
            if (mKet_noi == null)
            {
                mKet_noi = new SqlConnection();
                mKet_noi.ConnectionString = Chuoi_CSDL;
            }

            if (mKet_noi.State == ConnectionState.Open)
                mKet_noi.Close();
                mKet_noi.Open();
            
            DataTable dt;
            dt = new DataTable();
            try
            {
                SqlCommand command = new SqlCommand(sp_name, mKet_noi);
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = 1200;//----------19/07/2012--Fix lỗi Timeout--
                if (paramse != null)
                {
                    int len = paramse.Length - 1;
                    int i;

                    for (i = 0; i <= len; i++)
                    {
                        if (paramse[i] +""!= "")
                        {
                            command.Parameters.Add(new SqlParameter(paramse[i], values[i]));
                        }
                    }
                }
                SqlDataAdapter dap = new SqlDataAdapter(command);
                dap.Fill(dt);
            }
            catch (Exception ex)
            {
                string As = ex.ToString();
                LPV_POS.Module.MessageBox_LPV.Show("Thông báo\n" + sp_name + "\n" + ex.Message);
                dt = null;
            }
            finally
            {
                mKet_noi.Close();
            }
            return dt;
        }

        // Lay du lieu tu 1 store proceduce, ket qua tra ve la 1 dataset
        public DataSet SP_SelectDataset(String sp_name, String[] paramse, Object[] values)
        {
            if (mKet_noi == null)
            {

                mKet_noi = new SqlConnection();
                mKet_noi.ConnectionString = Chuoi_CSDL;
            }

            if (mKet_noi.State == ConnectionState.Open)
                mKet_noi.Close();
                mKet_noi.Open();
            
            DataSet ds = new DataSet();
            try
            {
                SqlCommand command = new SqlCommand(sp_name, mKet_noi);
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = 1200;//----------19/07/2012--Fix lỗi Timeout--
                if (paramse != null)
                {
                    int len = paramse.Length - 1;
                    int i;
                    for (i = 0; i <= len; i++)
                    {
                        if (paramse[i] != "")
                        {
                            command.Parameters.Add(new SqlParameter(paramse[i], values[i]));
                        }

                    }
                }
                SqlDataAdapter dap = new SqlDataAdapter(command);
                dap.SelectCommand = command;
                dap.Fill(ds);
                command.Dispose();
                dap.Dispose();
            }
            catch (Exception ex)
            {
                string s = ex.ToString();
                LPV_POS.Module.MessageBox_LPV.Show("Thông báo\n" + sp_name + "\n" + ex.Message);
                ds = null;
            }
            finally
            {
                mKet_noi.Close();
            }

            return ds;
        }

        //Thêm, xóa, sửa dữ liệu đến database. Trả về -1 nếu thất bại
        public int SP_Update(String sp_name, String[] paramse, Object[] values)
        {
            int result = 0;
            if (mKet_noi == null)
            {
                mKet_noi = new SqlConnection();
                mKet_noi.ConnectionString = Chuoi_CSDL;
            }
            if (mKet_noi.State != ConnectionState.Open)
            {
                mKet_noi.Open();
            }
            SqlCommand command = new SqlCommand(sp_name, mKet_noi);
            command.CommandType = CommandType.StoredProcedure;
            command.CommandTimeout = 1200;
            SqlTransaction MyTrans = mKet_noi.BeginTransaction();
            command.Transaction = MyTrans;
            try
            {
                if (paramse != null)
                {
                    int len = paramse.Length - 1;
                    int i;
                    for (i = 0; i <= len; i++)
                    {
                        command.Parameters.Add(new SqlParameter(paramse[i], values[i]));
                    }
                }
                command.Parameters.Add(new SqlParameter("@RETURN_VALUE", SqlDbType.Int, 0));
                command.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;

              object irt=  command.ExecuteNonQuery();
                MyTrans.Commit();
                result = (int)command.Parameters["@RETURN_VALUE"].Value;
            }
            catch (Exception ex)
            {

                LPV_POS.Module.MessageBox_LPV.Show("Thông báo\n" + sp_name + "\n" + ex.Message);
               // return 2; //thất bại
                return -1; //thất bại
            }
            finally
            {
                command.Dispose();
                MyTrans.Dispose();
                command = null;
                mKet_noi.Close();
            }
            return result;
        }

        #region "   SP_Update"
        public int SP_Update(string spName, string[] param, object[] values, ref string _ioutput)
        {//                                                                         string _ioutput = "@ireturn";
            int result = 0;
            SqlConnection con = new SqlConnection(Chuoi_CSDL);
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(spName, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 1200;//----------19/07/2012--Fix lỗi Timeout--
                if (param != null)
                {
                    for (int i = 0; i < param.Length; i++)
                    {
                        if (param[i] + "" != "")
                            cmd.Parameters.Add(new SqlParameter(param[i], values[i]));
                    }
                }
                //cmd.Parameters.Add(new SqlParameter(param[i], values[i]));
                if (_ioutput != "")
                {
                    if (_ioutput.IndexOf("@") != 0)
                        _ioutput = "@" + _ioutput;
                    ///
                    SqlParameter parm3 = new SqlParameter(_ioutput, SqlDbType.NVarChar);
                    parm3.Size = 300;
                    parm3.Value = "hiệu.lê";
                    parm3.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(parm3);
                }

                result = cmd.ExecuteNonQuery();
                if (_ioutput != "")
                {
                    object Tuine = cmd.Parameters["" + _ioutput].Value;
                    _ioutput = Tuine + "";
                }
            }
            catch (Exception ex)
            {
                LPV_POS.Module.MessageBox_LPV.Show("Thông báo\n" + spName + "\n" + ex.Message);
            }
            if (con.State == ConnectionState.Open)
                con.Close();
            return result;


        }        
        #endregion

        //Lay du lieu tu 1 store procedure, ket qua tra ve la 1 gia tri ma thoi trẻ về bằng 2 neu thất bại
        public int SP_SelectReturnOne(string sp_name, string[] paramse, object[] values)
        {
           
            int result = 0;
            if (mKet_noi == null)
            {
                mKet_noi = new SqlConnection();
                mKet_noi.ConnectionString = Chuoi_CSDL;
            }
            if (mKet_noi.State != ConnectionState.Open)
            {
                mKet_noi.Open();
            }
            SqlCommand command = new SqlCommand(sp_name, mKet_noi);
            command.CommandType = CommandType.StoredProcedure;
            command.CommandTimeout = 1200;
            SqlTransaction MyTrans = mKet_noi.BeginTransaction();
            command.Transaction = MyTrans;
            try
            {
                if (paramse != null)
                {
                    int len = paramse.Length - 1;
                    int i;
                    for (i = 0; i <= len; i++)
                    {
                        command.Parameters.Add(new SqlParameter(paramse[i], values[i]));
                    }
                }

                MyTrans.Commit();

                int.TryParse(command.ExecuteScalar() + "", out result);
                
            }
            catch (Exception ex)
            {
                LPV_POS.Module.MessageBox_LPV.Show("Thông báo\n" + sp_name + "\n" + ex.Message);
                return -1; //thất bại
            }
            finally
            {
                command.Dispose();
                MyTrans.Dispose();
                command = null;
                mKet_noi.Close();
            }
            return result;
        }
        //------------------------------------------------------

        //insert  mot datatable vao table trong csdl
        public int SP_inser_alltable(string table_name, DataTable  tb)
        {
            int ketqua =-1;
            try
            {
                if (mKet_noi == null)
                {
                    mKet_noi = new SqlConnection();
                    mKet_noi.ConnectionString = Chuoi_CSDL;
                }
                if (mKet_noi.State != ConnectionState.Open)
                {
                    mKet_noi.Open();
                }

                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand("Select * from " + table_name, mKet_noi);
                SqlCommandBuilder objbuilder = new SqlCommandBuilder(da);
                if (tb.Rows.Count >= 0)
                {
                  
                    da.Update(tb);
                    mKet_noi.Close();
                    ketqua = 1;
                   
                }
                
            }
            catch (Exception ex)
            {
                LPV_POS.Module.MessageBox_LPV.Show("Thông báo\n" + table_name + "\n" + ex.Message);
                return -1;
            }
            finally
            {
                mKet_noi.Close();
            }
            return ketqua;
           
        }

        //delete  mot datatable vao table trong csdl
        public int SP_delete_talltable(string table_name, DataTable tb, string table_id)
        {
            int ketqua = -1;
            try
            {
                if (mKet_noi == null)
                {
                    mKet_noi = new SqlConnection();
                    mKet_noi.ConnectionString = Chuoi_CSDL;
                }
                if (mKet_noi.State != ConnectionState.Open)
                {
                    mKet_noi.Open();
                }

             string str_del = @"Delete " + table_name + " where  " + table_id +  " = @id ";
             SqlDataAdapter da = new SqlDataAdapter();
             SqlCommand cmd = new SqlCommand(str_del, mKet_noi);
             cmd.CommandType = CommandType.Text;
             cmd.Parameters.Add("@id", SqlDbType.Int, 4, table_id);
             da.DeleteCommand = cmd;
             da.Update(tb);
                    ketqua = 1;

            }
            catch (Exception ex)
            {
                LPV_POS.Module.MessageBox_LPV.Show("Thông báo\n" + table_name + "\n" + ex.Message);
                return -1;
            }
            finally
            {
                mKet_noi.Close();
            }
            return ketqua;

        }

        //-----------------------------------------------------

        #region "   Connect"
        void Connect()
        {
            string strFile = Application.StartupPath;
            strFile = strFile + "\\login.xml";
            if (System.IO.File.Exists(strFile))
            {
                #region Read
                DataSet dsLogin = new DataSet();
                dsLogin.ReadXml(strFile);
                Chuoi_CSDL = Chuoi_CSDL.Replace("@Server", dsLogin.Tables[0].Rows[0]["Server"] + "");
                Chuoi_CSDL = Chuoi_CSDL.Replace("@Database", dsLogin.Tables[0].Rows[0]["Database"] + "");
                string sUser = dsLogin.Tables[0].Rows[0]["uid"] + "";
                string sPwd = new cpublic().GiaiMa(sUser, dsLogin.Tables[0].Rows[0]["Password"] + "");
                Chuoi_CSDL = Chuoi_CSDL.Replace("@uid", sUser);
                Chuoi_CSDL = Chuoi_CSDL.Replace("@Password", sPwd);
                #endregion
            }
            else
            {
                #region Write: tao xml
                DataTable tbLogin = new DataTable("login");
                tbLogin.Columns.Add("Server");
                tbLogin.Columns.Add("Database");
                tbLogin.Columns.Add("uid");
                tbLogin.Columns.Add("Password");
                DataRow drLogin = tbLogin.NewRow();
                drLogin["Server"] = ".";
                drLogin["Database"] = "LPV_HRM";
                drLogin["uid"] = "sa";
                drLogin["Password"] = "sa";
                tbLogin.Rows.Add(drLogin);
                tbLogin.WriteXml(strFile);
                this.StartFile(strFile);
                #endregion
            }
        }
        #endregion

        #region "   StartFile"
        /// <summary>
        /// Mở file để chỉnh sửa
        /// </summary>
        /// <param name="fileName">Tên file: login.xml</param>
        private void StartFile(string fileName)
        {
            using (System.Diagnostics.Process proc = new System.Diagnostics.Process())
            {
                proc.StartInfo.FileName = fileName;
                proc.Start();
            }
        }
        #endregion

        #region "show data on combobox"
        // hien thi ds nhom chu len combobox
       public  void f_showdataoncombox(AXCombobox cb, DataTable tb, string value, string display)
        {
            cb.Properties.Items.Clear();
            cb.Datasource = tb;
            cb.DisplayField = display;
            cb.ValueFiled = value;
            cb._Init();
        }
       public void f_showdataoncomcheckbox(PLMultiCombobox cb, DataTable tb, string value, string display)
       {
           cb.Properties.Items.Clear();
           cb.DataSource = tb;
           cb.DisplayField = display;
           cb.ValueField = value;
           cb._init();
       }
        #endregion

        #region Lay du lieu tu 1 TableName, ket qua tra ve la 1 dataset

        /// <summary>
        /// Lấy dữ liệu từ TableName
        /// </summary>
        /// <param name="TableName">Tên Table</param>
        /// <returns>Dataset theo TableName</returns>
        public DataSet sql_SelectDataset(String TableName)
       {
           if (mKet_noi == null)
           {

               mKet_noi = new SqlConnection();
               mKet_noi.ConnectionString = Chuoi_CSDL;
           }

           if (mKet_noi.State != ConnectionState.Open)
           {
               mKet_noi.Open();
           }
           DataSet ds = new DataSet();
           try
           {
               string sql = "select * from " + TableName ;
               SqlCommand command = new SqlCommand(sql, mKet_noi);
               SqlDataAdapter dap = new SqlDataAdapter(command);
               dap.Fill(ds);
               command.Dispose();
               dap.Dispose();
           }
           catch (Exception ex)
           {
               string s = ex.ToString();
               LPV_POS.Module.MessageBox_LPV.Show("Thông báo\n" + TableName + "\n" + ex.Message);
               ds = null;
           }
           finally
           {
               mKet_noi.Close();
           }

           return ds;
       }

        /// <summary>
        /// Lấy dữ liệu từ TableName theo ID
        /// </summary>
        /// <param name="TableName">Tên Table</param>
        /// <param name="IDKeyName">Tên ID Cần Lấy</param>
        /// <param name="IDKey">Giá trị ID</param>
        /// <returns>Dataset theo TableName</returns>
        public DataSet sql_SelectDataset(String TableName, string IDKeyName, int IDKey)
       {
           if (mKet_noi == null)
           {

               mKet_noi = new SqlConnection();
               mKet_noi.ConnectionString = Chuoi_CSDL;
           }

           if (mKet_noi.State != ConnectionState.Open)
           {
               mKet_noi.Open();
           }
           DataSet ds = new DataSet();
           try
           {
               string sql = "select * from " + TableName + " where " + IDKeyName + "=" + IDKey ;
               SqlCommand command = new SqlCommand(sql, mKet_noi);
               SqlDataAdapter dap = new SqlDataAdapter(command);
               dap.Fill(ds);
               command.Dispose();
               dap.Dispose();
           }
           catch (Exception ex)
           {
               string s = ex.ToString();
               LPV_POS.Module.MessageBox_LPV.Show("Thông báo\n" + TableName + "\n" + ex.Message);
               ds = null;
           }
           finally
           {
               mKet_noi.Close();
           }

           return ds;
       }

        /// <summary>
        /// Lấy dữ liệu từ TableName theo ID
        /// </summary>
        /// <param name="TableName">Tên Table</param>
        /// <param name="IDKeyName">Tên ID Cần Lấy</param>
        /// <param name="IDKey">Giá trị ID</param>
        /// <returns>Dataset theo TableName</returns>
        public DataSet sql_SelectDataset(String TableName, string IDKeyName, string IDKey)
        {
            if (mKet_noi == null)
            {

                mKet_noi = new SqlConnection();
                mKet_noi.ConnectionString = Chuoi_CSDL;
            }

            if (mKet_noi.State != ConnectionState.Open)
            {
                mKet_noi.Open();
            }
            DataSet ds = new DataSet();
            try
            {
                string sql = "select * from " + TableName + " where " + IDKeyName + "='" + IDKey+"'";
                SqlCommand command = new SqlCommand(sql, mKet_noi);
                SqlDataAdapter dap = new SqlDataAdapter(command);
                dap.Fill(ds);
                command.Dispose();
                dap.Dispose();
            }
            catch (Exception ex)
            {
                string s = ex.ToString();
                LPV_POS.Module.MessageBox_LPV.Show("Thông báo\n" + TableName + "\n" + ex.Message);
                ds = null;
            }
            finally
            {
                mKet_noi.Close();
            }

            return ds;
        }

        /// <summary>
        /// Thực thi câu Query
        /// </summary>
        /// <param name="Query">Câu lệnh SQL</param>
        /// <returns>int</returns>
        public int sql_Excute(string Query)
        {
            if (mKet_noi == null)
            {

                mKet_noi = new SqlConnection();
                mKet_noi.ConnectionString = Chuoi_CSDL;
            }

            if (mKet_noi.State != ConnectionState.Open)
            {
                mKet_noi.Open();
            }
            int i = -1;
            try
            {
                SqlCommand command = new SqlCommand(Query, mKet_noi);
                i = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string s = ex.ToString();
                LPV_POS.Module.MessageBox_LPV.Show("Thông báo\n" + Query + "\n" + ex.Message);
                i = -1;
            }
            finally
            {
                mKet_noi.Close();
            }

            return i;
        }

        #endregion
    }
}



